
alter function dbo.f_cashdiffacc(
  @nyear integer,
  @nmonth integer
)
returns 
   @temp_table table
  (
    subjectid integer ,
    bmbala decimal(18,2), 
    bysumdebit decimal(18,2),
    bysumcredit decimal(18,2), 
    tmsumdebit decimal(18,2), 
    tmsumcredit decimal(18,2),
    tmsum decimal(18,2), 
    standardbala decimal(18,2), 
    fbmbala decimal(18,2), 
    fbysumdebit decimal(18,2), 
    fbysumcredit decimal(18,2), 
    ftmsumdebit decimal(18,2), 
    ftmsumcredit decimal(18,2), 
    ftmsum decimal(18,2), 
    foreignbala decimal(18,2)
  )
as  
  
begin
declare @credyearmonth varchar(8)
declare @idifference decimal(18,2)
declare @nmoneyid integer
declare @subjectid integer ,
        @bmbala decimal(18,2), 
        @bysumdebit decimal(18,2),
        @bysumcredit decimal(18,2), 
        @tmsumdebit decimal(18,2), 
        @tmsumcredit decimal(18,2),
        @tmsum decimal(18,2), 
        @standardbala decimal(18,2), 
        @fbmbala decimal(18,2), 
        @fbysumdebit decimal(18,2), 
        @fbysumcredit decimal(18,2), 
        @ftmsumdebit decimal(18,2), 
        @ftmsumcredit decimal(18,2), 
        @ftmsum decimal(18,2), 
        @foreignbala decimal(18,2)

declare @local_tab table(subjectid integer,
                         moneyid integer,
                         bmbala decimal(18,2) ,
                         bysumdebit decimal(18,2) ,
                         bysumcredit decimal(18,2),
                         tmsumdebit decimal(18,2),
                         tmsumcredit decimal(18,2),
                         tmsum decimal(18,2),
                         standardbala decimal(18,2),
                         fbmbala decimal(18,2),
                         fbysumdebit decimal(18,2),
                         fbysumcredit decimal(18,2),
                         ftmsumdebit decimal(18,2),
                         ftmsumcredit decimal(18,2),
                         ftmsum decimal(18,2),
                         foreignbala decimal(18,2))

  insert into @local_tab
  select
    a.subjectid,
    s.moneyid,
    sum(c.bmbala        ) as bmbala       ,
    sum(c.bysumdebit    ) as bysumdebit   ,
    sum(c.bysumcredit   ) as bysumcredit  ,
    sum(c.tmsumdebit    ) as tmsumdebit   ,
    sum(c.tmsumcredit   ) as tmsumcredit  ,
    sum(c.tmsum         ) as tmsum        ,
    sum(c.standardbala  ) as standardbala ,
    sum(c.fbmbala       ) as fbmbala      ,
    sum(c.fbysumdebit   ) as fbysumdebit  ,
    sum(c.fbysumcredit  ) as fbysumcredit ,
    sum(c.ftmsumdebit   ) as ftmsumdebit  ,
    sum(c.ftmsumcredit  ) as ftmsumcredit ,
    sum(c.ftmsum        ) as ftmsum       ,
    sum(c.foreignbala   ) as foreignbala 
    from td_account a
    left outer join tft_mscash c on c.accountid=a.accid
    left outer join tfd_subject s on a.subjectid=s.subjectid
    where c.nyear=@nyear and c.nmonth=@nmonth
    group by a.subjectid,s.moneyid

  declare cur_diff cursor for select * from @local_tab
  open cur_diff 
  fetch next from cur_diff into @subjectid       ,
    @nmoneyid        ,          @bmbala          ,         @bysumdebit      ,
    @bysumcredit     ,          @tmsumdebit      ,         @tmsumcredit     ,
    @tmsum           ,          @standardbala    ,         @fbmbala         ,
    @fbysumdebit     ,          @fbysumcredit    ,         @ftmsumdebit     ,
    @ftmsumcredit    ,          @ftmsum          ,         @foreignbala 
  while @@fetch_status = 0 
  begin    
    if (@nmoneyid=0)
    begin
      set @fbmbala = @bmbala
      set @fbysumdebit = @bysumdebit
      set @fbysumcredit = @bysumcredit
      set @ftmsumdebit = @tmsumdebit
      set @ftmsumcredit = @tmsumcredit
      set @ftmsum = @tmsum
    end
    else
      set @standardbala = @foreignbala
   insert into @temp_table values(@subjectid,@bmbala,@bysumdebit,
                                  @bysumcredit,@tmsumdebit,@tmsumcredit,
                                  @tmsum,@standardbala,@fbmbala,@fbysumdebit,
                                  @fbysumcredit,@ftmsumdebit,@ftmsumcredit,@ftmsum,@foreignbala)

   fetch next from cur_diff into @subjectid       ,
     @nmoneyid         ,         @bmbala          ,         @bysumdebit      ,
     @bysumcredit     ,          @tmsumdebit      ,         @tmsumcredit     ,
     @tmsum           ,          @standardbala    ,         @fbmbala         ,
     @fbysumdebit     ,          @fbysumcredit    ,         @ftmsumdebit     ,
     @ftmsumcredit    ,          @ftmsum          ,         @foreignbala 
  end
 return
end




